package dreampack.dataQuery;

import java.sql.*;
import java.util.*;

import dreampack.*;
import dreampack.dataType.*;

public class MessageQuery {
	public MessageQuery() { }
	
	public static MessageType qeuryById(DreamDB dreamDb, int msgId) throws Exception {
		MessageType msg = null;
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select * from t_message where id=?";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, msgId);
			rs = prepStmt.executeQuery();
			if (rs.next()) {
				msg = new MessageType();
				msg.init(dreamDb, rs);
				return msg;
			}
			else
				return null;
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closeStatement(prepStmt);
			dreamDb.closeConnection(con);
		}
	}
	
	public static Collection querySendedMsgsByUser(DreamDB dreamDb, UserType user) throws Exception{
		ArrayList messages = new ArrayList();
		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		int userId = user.getId();
		
		try{
			con = dreamDb.getConnection();
			String selectStatement = "select * from t_message where `author_id`=? order by lastmodify";
			prepStmt = con.prepareStatement(selectStatement);
			prepStmt.setInt(1, userId);
			rs = prepStmt.executeQuery();
			while (rs.next()){
				MessageType msg = new MessageType();
				msg.init(dreamDb, rs);
				messages.add(msg);
			}
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closePrepStmt(prepStmt);
			dreamDb.closeConnection(con);
		}
		//Collections.sort(messages);
		return messages;
	}
	
	public static Collection queryAllMsgs(DreamDB dreamDb) throws Exception{
		ArrayList messages = new ArrayList();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try{
			con = dreamDb.getConnection();
			String sql = "select * from t_message order by lastmodify";
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()){
				MessageType msg = new MessageType();
				msg.init(dreamDb, rs);
				messages.add(msg);
			}
		}finally{
			dreamDb.closeResultSet(rs);
			dreamDb.closeStatement(stmt);
			dreamDb.closeConnection(con);
		}
		//Collections.sort(messages);
		return messages;
	}
}
